"""
Case Type   : 系统表
Case Name   : 测试系统表pg_job重新执行的记录
Create At   : 2024/07/24
Owner       : lvlintao666
Description :
    1.修改参数
    2.新建表及定时任务
    3.kill主机，将standby重启为新的主机
    4.连接新主机，查询表和pg_job表
    5.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
History     :
"""
import os
import time
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro

Pri_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(3 != Pri_SH.get_node_num(), '单机环境不执行')
class SystemTable(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f"-----{os.path.basename(__file__)} start-----")
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.sh_standby1 = CommonSH('Standby1DbUser')
        self.standby1_node = Node('Standby1DbUser')
        self.standby2_node = Node('Standby2DbUser')
        self.primary_node = Node('PrimaryDbUser')
        self.primary_root = Node('PrimaryRoot')
        self.constant = Constant()
        self.com = Common("Standby1DbUser")
        self.job_id = ""
        self.env_path = macro.DB_ENV_PATH
        self.tb_name = "tb_system_table_case0075"
        self.job_sql = f"select PKG_SERVICE.JOB_SUBMIT(null," \
                       f"'select pg_sleep(40);insert into {self.tb_name} values(1);'," \
                       f"sysdate,'sysdate + 120.0/24/3600')"
        self.instance_path = macro.DB_INSTANCE_PATH

    def test_system_table(self):
        text = "-----step1:修改guc参数;expect:修改参数成功-----"
        self.logger.info(text)
        mod_msg = Pri_SH.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'sync_config_strategy=none_node')
        self.logger.info(mod_msg)
        self.assertTrue(mod_msg, '执行失败:' + text)
        restart_msg = Pri_SH.restart_db_cluster()
        self.logger.info(restart_msg)

        text = "-----step2:建表及定时任务;expect:成功-----"
        self.logger.info(text)
        create_cmd = f"drop table if exists {self.tb_name};" \
                     f"create table {self.tb_name} (a int);" \
                     f"{self.job_sql}"
        self.logger.info(create_cmd)
        create_result = self.sh_primary.execut_db_sql(create_cmd)
        self.logger.info(create_result)
        self.job_id = create_result.splitlines()[-2].strip()
        self.logger.info(self.job_id)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, create_result,
                      f'执行失败: {text}')

        text = "-----step3:kill主机，重启备机为主机;expect:重启成功-----"
        self.logger.info(text)
        msg = self.com.kill_pid(self.primary_root, '9')
        self.logger.info(msg)
        self.assertEqual(msg, '')
        result = self.sh_primary.get_db_cluster_status()
        self.logger.info(result)
        self.assertIn("Unavailable", result, f'执行失败: {text}')

        execute_cmd = f"source {self.env_path};" \
                      f"gs_ctl restart -D {self.instance_path} -M primary;"
        self.logger.info(execute_cmd)
        execute_result = self.standby1_node.sh(execute_cmd).result()
        self.logger.info(execute_result)
        self.assertIn(self.constant.RESTART_SUCCESS_MSG, execute_result)

        text = "-----step4:连接新主机，查询pg_job;expect:成功-----"
        self.logger.info(text)
        query_cmd = f"select count(*) from pg_job where job_id = {self.job_id};"
        time.sleep(60)
        res_cmd = f"select count(*) from {self.tb_name};"
        self.logger.info(res_cmd)
        self.logger.info(query_cmd)
        query_result = self.sh_standby1.execut_db_sql(query_cmd)
        res_result = self.sh_standby1.execut_db_sql(res_cmd)
        self.logger.info(query_result)
        self.logger.info(res_result)
        self.assertGreater(int(query_result.splitlines()[-2]), 0,
                           f'执行失败: {text}')
        self.assertGreater(int(res_result.splitlines()[-2]), 0,
                           f'执行失败: {text}')

    def tearDown(self):
        text = "-----step5:清理环境;expect:成功-----"
        self.logger.info(text)
        stop_cmd = f"source {self.env_path};" \
                   f"gs_ctl stop -D {self.instance_path};"
        self.logger.info(stop_cmd)
        stop_result = self.standby1_node.sh(stop_cmd).result()
        self.logger.info(stop_result)
        self.assertIn(self.constant.STOP_STATUS_MSG, stop_result,
                      f'执行失败: {text}')
        self.sh_primary.restart_db_cluster()
        build_cmd = f"source {self.env_path};" \
                     f"gs_ctl build;"
        self.logger.info(build_cmd)
        build1_msg = self.standby1_node.sh(build_cmd).result()
        self.logger.info(build1_msg)
        build2_msg = self.standby2_node.sh(build_cmd).result()
        self.logger.info(build2_msg)
        result = self.sh_primary.get_db_cluster_status()
        self.assertIn(self.constant.START_STATUS_MSG, result, f'执行失败: {text}')

        clear_cmd = f"delete from pg_job where job_id = {self.job_id};" \
                    f"drop table {self.tb_name}"
        self.logger.info(clear_cmd)
        clear_msg = self.sh_primary.execut_db_sql(clear_cmd)
        self.logger.info(clear_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clear_msg, f'执行失败: {text}')
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')

